# 作者：吾那尔汗
# 名称：各学校学籍报表统计脚本
# 版本：v1.0

import os
from openpyxl import load_workbook
import numpy as np

# 指定文件夹路径
folder_path = 'data'

# 每一个年龄段容器
year_0 = []
year_1 = []
year_2 = []
year_3 = []
year_4 = []
year_5 = []
year_6 = []
year_7 = []
year_8 = []
year_9 = []
year_10 = []
year_11 = []
year_12 = []
year_13 = []
year_14 = []
year_15 = []
year_16 = []
year_17 = []
year_18 = []
year_19 = []


# 遍历所有文件读取Excel文件内容
for filename in os.listdir(folder_path):
    if filename.endswith('.xlsx') or filename.endswith('.xls'):  # 只处理 Excel 文件
        file_path = os.path.join(folder_path, filename)
        
        # 加载工作簿
        wb = load_workbook(file_path)
        
        # 选择 Sheet1
        sheet = wb['Sheet1']
        
        # 读取第8行至第33行，第1列至第36列的数据
        data = []
        for row in sheet.iter_rows(min_row=14, max_row=33, min_col=1, max_col=33):
            row_data = list()
            for cell in row:
                if isinstance(cell.value,int):
                    row_data.append(cell.value) 
            data.append(row_data)

        # 添加并计算
        year_0.append(data[0])
        year_1.append(data[1])
        year_2.append(data[2])
        year_3.append(data[3])
        year_4.append(data[4])
        year_5.append(data[5])
        year_6.append(data[6])
        year_7.append(data[7])
        year_8.append(data[8])
        year_9.append(data[9])
        year_10.append(data[10])
        year_11.append(data[11])
        year_12.append(data[12])
        year_13.append(data[13])
        year_14.append(data[14])
        year_15.append(data[15])
        year_16.append(data[16])
        year_17.append(data[17])
        year_18.append(data[18])
        year_19.append(data[19])


        # 打印数据到屏幕
        # print(f"文件: {filename}")

# print(np.sum(year_0,axis=0)[0])

# 引入openpyxl模块
from openpyxl import Workbook

# 假定result.xlsx已经存在并包括一个Sheet1
result_wb = load_workbook('summary.xlsx')
result_sheet = result_wb['Sheet1']

# 将计算结果年份year_0列表的第一个元素写入到对应的单元格
# result_sheet['B14'] = np.sum(year_0,axis=0)[0]


def writer(line,year):
    # 为指定的行写入指定岁数的数据求和结果
    # line 指定行
    # year 指定岁数
    excel_column_names = [
    "B", "C", "D", "E", "F", "G", "H", "I", "J",
    "K", "M", "N","P", "Q", "R", "S", "T","U", 
    "V", "Y", "Z", "AA", "AC", "AD","AE","AF","AG"]

    for index,value in enumerate(excel_column_names):
        # print(index,value)
        # 求和结果写入指定单元格
        result_sheet[f'{value}{line}'] = np.sum(year,axis=0)[index]

    # 保存修改
    result_wb.save('summary.xlsx')
    

# 调用函数写入汇总数据
writer(14,year_0) # 14行写入0岁汇总数据
writer(15,year_1) # 15行写入1岁汇总数据
writer(16,year_2)
writer(17,year_3)
writer(18,year_4)
writer(19,year_5)
writer(20,year_6)
writer(21,year_7)
writer(22,year_8)
writer(23,year_9)
writer(24,year_10)
writer(25,year_11)
writer(26,year_12)
writer(27,year_13)
writer(28,year_14)
writer(29,year_15)
writer(30,year_16)
writer(31,year_17)
writer(32,year_18)
writer(33,year_19)

# 关闭工作簿
result_wb.close()

